package com.rg1906.dao.Impl;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.rg1906.entity.QueryClazz;
import com.rg1906.entity.QueryGrade;
import com.rg1906.utils.DataSourceC3P0ThreadLocal;

public class InsertStudentMessageDaoImpl {
	DataSource dataSource = DataSourceC3P0ThreadLocal.getDataSource();
	QueryRunner qr = new QueryRunner(dataSource);
	public int getGid(String gid) throws SQLException {
		String sqlGid="select id from t_grade where gradename=?";
		int gradeGid = qr.query(sqlGid, new ScalarHandler<>(),gid);
		return gradeGi按时打大所d;
	}
	
	public int getCid(String cid) throws SQLException {
		String sqlCid="select id from t_grade where gradename=?";
		int gradeCid = qr.query(sqlCid, new ScalarHandler<>(),cid);
		return gradeCid;
	}
	//求最大学号
	public String getMaxNum(String gid, String cid) throws SQLException {
		int gradeGid = getGid(gid);
		int gradeCid = getCid(cid);
		String sql="select MAX(studentNum) from t_student  where gid=? and cid=?";
		Object[] par= {gradeGid,gradeCid};
		String maxStudentNum = qr.query(sql, new ScalarHandler<>(),par);
		return maxStudentNum;
	}
	public List<QueryGrade> QueryNianji() throws SQLException {
		String sql="select id,gradename from t_grade where gid is null";
		List<QueryGrade> list = qr.query(sql, new BeanListHandler<>(QueryGrade.class));
		return list;
	}
	//获取年级框change后的id 值
	public int queryClazz(String gid1) throws SQLException {
		String sql="select id from t_grade where gradename=?";
		return qr.query(sql, new ScalarHandler<>(),gid1);
	}

	public List<QueryClazz> queryClazzs(int gradeID) throws SQLException {
		String sql="select gradename from t_grade where gid=?";
		return qr.query(sql,new BeanListHandler<>(QueryClazz.class),gradeID);
	}
	//添加学生 
	public int insertStudent(String realname, int gradeCid, int gradeGid, String sNum, String phone, String address,String sex, String age) throws SQLException {
		Object[] par= {realname,gradeCid,gradeGid,sNum,phone,address,sex,age};
		String sql="insert into t_student (realname,cid,gid,studentNum,phone,address,sex,age) values(?,?,?,?,?,?,?,?)";
		return  qr.update(sql,par);
	}
	//将学生的信息添加到user 表中
	public int insertUser(String sNum, String img) throws SQLException {
		//添加学号sNum=111 到user表中 为登录账号
		String sql1="insert into t_user (loginName,rid,img) values (?,?,?)";
		int i = qr.update(sql1,sNum,4,img);
		return i;
	}
	public int getUserId(String sNum) throws SQLException {
		//再查找user表中的id 为 学生表中的 uid
		String sql2="select id from t_user where loginName=?";
		int  id = qr.query(sql2, new ScalarHandler<>(),sNum);
		return id;	
	}
	//查到的id为student表中的uid
	public int InsertStudentUid(int userId, String realname) throws SQLException {
		String sql3="update t_student set uid=? where realname=?";
		int update = qr.update(sql3,userId,realname);
		return update;
		
	}

	public int insertTScore(String sNum) throws SQLException {
		int ii=0;
		String sqlCount="select count(0) from t_course";
		Long i = qr.query(sqlCount,new ScalarHandler<>());
		for(int j=1;j<=i;j++) {
			String sql="insert into t_score (courseNum,studentNum) values(?,?) ";
			Object[] par= {j,sNum};
			 ii = qr.update(sql,par);
			if(ii<=0) {
				return 0;
			}
		}
		return ii;
		
	}
}
